<?php
// +----------------------------------------------------------------------
// | Site:  [ http://www.yzmcms.com]
// +----------------------------------------------------------------------
// | Copyright: 袁志蒙工作室，并保留所有权利
// +----------------------------------------------------------------------
// | Author: YuanZhiMeng <214243830@qq.com>
// +---------------------------------------------------------------------- 
// | Explain: 这不是一个自由软件,您只能在不用于商业目的的前提下对程序代码进行修改和使用，不允许对程序代码以任何形式任何目的的再发布！
// +----------------------------------------------------------------------

defined('IN_YZMPHP') or exit('Access Denied'); 
yzm_base::load_controller('common', 'admin', 0);
yzm_base::load_sys_class('page','',0);
yzm_base::load_common('class/jst.class.php');
yzm_base::load_common('class/xlswriter.class.php');

class jst_dingzuo extends common {

	/**
	 * 配置信息
	 */

	public $config=[];
	public $jst=[];
	public $jst_arr=[];
	public $data=[];
	
	public function __construct(){
		//聚水潭列表
		$this->jst = get_jst_list();
		$k = 0;
		foreach($this->jst as $v){
			$this->jst_arr[$k]['id'] = $v['id'];
			$this->jst_arr[$k]['title'] = $v['short_name'];
			$k++;
		}
		parent::__construct();
	}
	
	public function where(){
		
	}
	
	
	//订做数据汇总
	public function dingzuo(){
		$of = input('post.field') ? input('post.field') : '7days';
		$or = input('post.order') ? input('post.order') : 'desc';
		$download = isset($_POST['download']) ? $_POST['download'] : 0;
		$page = input('post.page')?input('post.page'):1;
		$limit = input('post.limit')?input('post.limit'):30;
		$this_page = $page;
		$this_limit = $limit;
		$other_2 = $_POST["other_2"] ?? '';
		$sku_name = isset($_POST["sku_name"]) ? $_POST["sku_name"] : '';
		$brand = $_POST['brand']??'';
		if(!$brand){
			$brand = 'JEEP';
		}
		$buhuo_by_days = $_POST['buhuo_by_days'] ?? '3';
		$where = '1=1';

		
		if($other_2) {
			if(strpos($other_2,',')){
				$other_2 = str_replace(',','","',$other_2);
				$where .= ' AND other_2 in("'.$other_2.'")';
			}else{
				$where .= ' AND other_2 LIKE \'%'.$other_2.'%\'';
			}
		}
		$where_name = '';
		if($sku_name){
			if(strpos($sku_name,',')){
				$where .= ' and (';
				$where_temp = [];
				foreach(explode(',',$sku_name) as $v){
					$where_temp [] = ' name like "%'.$v.'%"';
				}
				$where .=implode(' or ',$where_temp);
				$where .=')';
			}else{
				$where .= ' and name like "%'.trim($sku_name).'%"';
			}
		}
		if($brand=='JEEP'){
			$where .=' and brand in("JEEP","JEEP半成品")';
		}elseif($brand=='马克华菲'){
			$where .=' and brand in("马克华菲","马克华菲半成品")';
		}else{
			if($brand=='ALL'){
				$where .=' and brand in("东方骆驼","增致牛仔 ZENGZHI","衣品天成","美丽衣橱","佑晟共享仓")';
			}else{
				$where .=' and brand in("'.$brand.'","佑晟共享仓")';
			}
		}

		if($_SESSION['admininfo']['rolename'] == '联营'){
			$where .=' and name like "%'.$_SESSION['admininfo']['realname'].'%"';
		}
		
		
		$where .=' and other_2 like "%订做%"';	


//echo $where;exit;
		if(!input('post.yzm_csrf_token')&&$download == 0){
			return include $this->admin_tpl('jst_dingzou/dingzuo');
		}
		
		if($download == 1){
			$limit = '0,10000';
		}else{
			$total = d('jst_sku_sales')->field('count(distinct(kuanhao)) total')->where($where)->one();
			$page = new page($total, $limit);
			$limit = $page->limit();
		}

		//线上商品销售
		//商品列表by other_2
		$sales_list_by_kuanhao = D('jst_sku_sales')->field('other_2,sum(1days) 1days,sum(2days) 2days,sum(3days) 3days,sum(7days) 7days,sum(30days) 30days,name,pic,kuanhao,sales_add_time')->where($where)->group('kuanhao')->limit($limit)->order("$of $or")->select();
		
		if(!$sales_list_by_kuanhao){
			$data = ['code'=>1,'msg'=>'暂无资料或者没有销售记录','count'=>$total,'data'=>[]];
			return_json($data);
		}
		
		$where2= '';
		if($download == 0){
			$kuanhao = array_column($sales_list_by_kuanhao,'kuanhao');
			$where2 = ' and substring_index( other_2, "#", 1 ) in("'.implode('","',$kuanhao).'")';
		}
		$sales_list_by_other_2 = D('jst_sku_sales')->field('other_2,sum(1days) 1days,sum(2days) 2days,sum(3days) 3days,sum(7days) 7days,sum(30days) 30days,name,pic,kuanhao,SUBSTRING_INDEX(SUBSTRING_INDEX(other_2, "#", -1),"-",1) color,SUBSTRING_INDEX(SUBSTRING_INDEX(other_2, "-", -1), "#", 1) AS size_info')->where($where.$where2)->group('other_2')->order('color,CASE WHEN size_info = "XS" THEN 1 WHEN size_info = "S" THEN 2 WHEN size_info = "M" THEN 3 WHEN size_info = "L" THEN 4 WHEN size_info = "XL" THEN 5 WHEN size_info = "2XL" THEN 6 WHEN size_info = "3XL" THEN 7 WHEN size_info = "4XL" THEN 8 WHEN size_info = "5XL" THEN 9 WHEN size_info = "6XL" THEN 10 ELSE size_info END')->select();
		$list_other_2 = [];
		foreach($sales_list_by_other_2 as $k=>$v){
			$list_other_2[$v['kuanhao']][$v['other_2']] = $v;
		}
		unset($sales_list_by_other_2);
		
		//商品库存by other_2 //建议生产数=7天销量/7*（生产周期+1天查货时间）-库存-生产中的库存
		//$buhuo = round($other[$buhuo_by_days.'days']/$buhuo_by_days*($other['cycel']+1)-$other['ys_qty']-$other['purchase_qty'],0);
		//$buhuo = $buhuo>0 ? $buhuo : 0;
		if($brand=='JEEP'){
			$where3='1=1 and brand in("JEEP","JEEP半成品")';
		}elseif($brand=='马克华菲'){
			$where3='1=1 and brand in("马克华菲","马克华菲半成品")';
		}else{
			$where3='1=1 and brand in("东方骆驼","增致牛仔 ZENGZHI","衣品天成","美丽衣橱","佑晟共享仓")';
		}
		$dingzuo_invertorys = D('jst_inventorys')->field('other_2,sku_id,qty,purchase_qty,in_qty,substring_index( other_2, "#", 1 ) AS kuanhao,brand')->where($where3.$where2)->select();
		//echo D('jst_inventorys')->lastsql();exit;
		$invertorys_other_2 = [];
		$in_qty_other_2 = [];
		$bcp_purchase_qty_other_2 =[];
		$cp_purchase_qty_other_2 = [];
		$bcp_sku_id = [];
		foreach($dingzuo_invertorys as $v){
			//主仓库存
			$invertorys_other_2[$v['kuanhao']] = isset($invertorys_other_2[$v['kuanhao']]) ? $invertorys_other_2[$v['kuanhao']] + $v['qty'] : $v['qty'];
			$invertorys_other_2[$v['other_2']] = isset($invertorys_other_2[$v['other_2']]) ? $invertorys_other_2[$v['other_2']] + $v['qty'] : $v['qty'];
			//采购途中
			if($v['brand'] == 'JEEP半成品'||$v['brand'] == '佑晟共享仓'||$v['brand'] == '马克华菲半成品'){
				$bcp_purchase_qty_other_2[$v['kuanhao']] = isset($bcp_purchase_qty_other_2[$v['kuanhao']]) ? $bcp_purchase_qty_other_2[$v['kuanhao']] + $v['purchase_qty'] : $v['purchase_qty'];
				$bcp_purchase_qty_other_2[$v['other_2']] =isset($bcp_purchase_qty_other_2[$v['other_2']]) ? $bcp_purchase_qty_other_2[$v['other_2']] + $v['purchase_qty'] : $v['purchase_qty'];
				$bcp_sku_id[$v['other_2']] = $v['sku_id'];
				//进货仓
				$in_qty_other_2[$v['kuanhao']] = isset($in_qty_other_2[$v['kuanhao']]) ? $in_qty_other_2[$v['kuanhao']] + $v['in_qty'] : $v['in_qty'];
				$in_qty_other_2[$v['other_2']] = isset($in_qty_other_2[$v['other_2']]) ? $in_qty_other_2[$v['other_2']] + $v['in_qty'] : $v['in_qty'];
			}else{
				$cp_purchase_qty_other_2[$v['kuanhao']] = isset($cp_purchase_qty_other_2[$v['kuanhao']]) ? $cp_purchase_qty_other_2[$v['kuanhao']] + $v['purchase_qty'] : $v['purchase_qty'];
				$cp_purchase_qty_other_2[$v['other_2']] = isset($cp_purchase_qty_other_2[$v['other_2']]) ? $cp_purchase_qty_other_2[$v['other_2']] + $v['purchase_qty'] : $v['purchase_qty'];
			}
		}
		unset($dingzuo_invertorys);
		
		if($brand=='JEEP'){
			$brand_bcp = 'JEEP半成品';
		}elseif($brand=='马克华菲'){
			$brand_bcp = '马克华菲半成品';
		}else{
			$brand_bcp = '佑晟共享仓';
		}
		$kuanhao = array_column($sales_list_by_kuanhao,'kuanhao');
		$where4 = ' and substring_index( s.other_2, "#", 1 ) in("'.implode('","',$kuanhao).'")';
		$bcp_sku = D('jst_sku')->alias('s')->field('s.other_2,ss.sku_id')->join(C('db_prefix').'jst_sku ss on s.other_2 = ss.other_2','left')->where('ss.brand="'.$brand_bcp.'"'.$where4)->select();
		foreach($bcp_sku as $sku){
			$bcp_sku_id[$sku['other_2']] = $sku['sku_id'];
		}
		
		
		
		$buhuo_cycel = get_config('buhuo_cycel');
		$i = 1;
		foreach($sales_list_by_kuanhao as $k=>&$v){
			$list = array_values($list_other_2[$v['kuanhao']]);
			$v['id'] = $i+($this_page-1)*$this_limit;
			$v['buhuo_cycel'] = $buhuo_cycel;
			foreach($list as &$val){
				$val['id'] = '↦';
				$val['kuanhao'] = $val['other_2'];
				$val['buhuo_cycel'] = $buhuo_cycel;
				$val['trend'] = avg_trend($val['3days']/3,$val['7days']/7);
				$val['inventorys'] = $invertorys_other_2[$val['other_2']]??0;
				$val['bcp_in_qty'] = $in_qty_other_2[$val['other_2']]??0;
				$val['bcp_purchase_qty'] = $bcp_purchase_qty_other_2[$val['other_2']]??0;
				$val['cp_purchase_qty'] = $cp_purchase_qty_other_2[$val['other_2']]??0;
				$val['bcp_sku_id'] = $bcp_sku_id[$val['other_2']]??'未上架/未销售';
				$val['name'] = $val['bcp_sku_id'];
				//默认算法：3天日均*30-待入库-待加工-半成品已采购-成品库存-备货仓库存
				$buhuo = round($val[$buhuo_by_days.'days']/$buhuo_by_days*$buhuo_cycel-$val['inventorys']-$val['bcp_in_qty']-$val['cp_purchase_qty']-$val['bcp_purchase_qty'],0);
				$val['buhuo_total_qty'] = $buhuo>0 ? $buhuo : 0;
				//计算款建议补货数
				if($buhuo>0){
					if(isset($v[$v['kuanhao']]['buhuo'])){
						$v[$v['kuanhao']]['buhuo'] += $buhuo;
					}else{
						$v[$v['kuanhao']]['buhuo'] = $buhuo;
					}
				}
				//可售天数 实际库存+加工大货中的+进货仓的=可用库存/7天平均=可售周期
				if($val[$buhuo_by_days.'days']>0){
					$val['sales_day'] = round(($val['bcp_in_qty']+$val['inventorys']+$val['cp_purchase_qty'])*$buhuo_by_days/$val[$buhuo_by_days.'days'],1);
				}else{
					$val['sales_day'] = '-';
				}
			} 
			$v['children'] = $list;
			$v['inventorys'] = $invertorys_other_2[$v['kuanhao']]??0;
			$v['bcp_in_qty'] = $in_qty_other_2[$v['kuanhao']]??0;
			$v['bcp_purchase_qty'] = $bcp_purchase_qty_other_2[$v['kuanhao']]??0;
			$v['cp_purchase_qty'] = $cp_purchase_qty_other_2[$v['kuanhao']]??0;
			$v['trend'] = avg_trend($v['3days']/3,$v['7days']/7);
			//默认算法：3天日均*30-待入库-待加工-半成品已采购-成品库存-备货仓库存
			//$buhuo = round($v[$buhuo_by_days.'days']/$buhuo_by_days*$buhuo_cycel-$v['bcp_in_qty']-$v['inventorys']-$v['cp_purchase_qty']-$v['bcp_purchase_qty'],0);
			$v['buhuo_total_qty'] = $v[$v['kuanhao']]['buhuo']??0;
			//可售天数 实际库存+加工大货中的+进货仓的=可用库存/7天平均=可售周期
			if($v[$buhuo_by_days.'days']>0){
				$v['sales_day'] = round(($v['bcp_in_qty']+$v['inventorys']+$v['cp_purchase_qty'])*$buhuo_by_days/$v[$buhuo_by_days.'days'],1);
			}else{
				$v['sales_day'] = '-';
			}
			$i++;
		}
		if($download==0){
			$data = ['code'=>0,'msg'=>'','count'=>$total,'data'=>$sales_list_by_kuanhao];
			return_json($data);
		}else{
			//print_r($sales_list_by_kuanhao);exit;
			$excel = new xlswriter('ys_dingzuo',1,'款式汇总');
			//['other_2','1days','2days','3days','7days','30days','cycel','ys_qty','buhuo_qty','sales_add_time'];
			$data1 = [];
			$data1_children = [];
			foreach($sales_list_by_kuanhao as $v1){
				foreach($v1['children'] as $val1){
					$val1['name'] = $v1['name'];
					$data_children[] = $val1;
				}
				$data[] = $v1;
			}
			$excel->insert_data($data);
			//print_r($sales_list_by_kuanhao);exit;
			$excel->add_sheet('ys_dingzuo_total','款式尺码明细',$data_children);
			$url = $excel->output();
			$data = ['code'=>0,'msg'=>'','url'=>$url];
			return_json($data);
		}
	}

}